using System;
using System.Data;
using System.Reflection;
using System.Collections;
using System.Collections.Specialized;
using System.Xml;
using System.Xml.XPath;

using Actions;
using Actions.Security;

namespace Actions.Databases
{
	/// <summary>
	/// Database - class for making universal connection to ADO.NET
	/// </summary>
	public class Database
	{
		private string			_id;
		private string			_title;
		
		private bool			_default;
		private bool			_autoconnect;

		private string			_provider = null;
		private string			_connectionType = null;
		private string			_connectionString = null;
				
		private string			_listStrategy = null;
		
		private Type			_cType = null;
		private IDbConnection	_c = null;
		
		public string info 
		{
			get 
			{
				return "<database id='" + _id + "' default='" + 
					_default + "'>" + _title + "</database>";	
			}
		}	
		
		public Database(Document d)
		{
			_id = d.GetValueOf("@id");
			_default = Boolean.Parse(d.GetValueOf("@default"));
			_autoconnect = Boolean.Parse(d.GetValueOf("@autoconnect"));
	
			_title = d.GetValueOf("Title");
			_provider = d.GetValueOf("ProviderAssembly");
			_connectionType = d.GetValueOf("ConnectionType");

			_listStrategy = d.GetValueOf("ListStrategy");

			_connectionString = "";
			string nvd = d.GetValueOf("ConnectionString/@nvdelimer");
			string pd = d.GetValueOf("ConnectionString/@paramdelimer");
			
			XPathNodeIterator i = d.Select("ConnectionString/param");
			while(i.MoveNext()) {
				_connectionString += i.Current.GetAttribute("name", "") + nvd + i.Current.Value;
				if(i.CurrentPosition < i.Count) _connectionString += pd;
			}			
		}
		
		public void connect()
		{
			if(_cType == null) {
				try {
					Assembly dbAssembly = Assembly.Load(_provider);
					_cType = dbAssembly.GetType(_connectionType, true);
					if (!typeof (IDbConnection).IsAssignableFrom(_cType))
						Manager.i.error("Database", new Exception());
				} catch(Exception e) {
					Manager.i.error("Database", e);
				}
			}
			
			try {
				_c = (IDbConnection) Activator.CreateInstance(_cType);
				_c.ConnectionString = _connectionString;
				_c.Open();
			} catch(Exception e) {		
				Manager.i.error("Database", e);
			}		
		}
		
		public void connectOnStart()
		{
			if(_autoconnect) this.connect();
		}
		
		public void disconnect()
		{
			if (_c != null) {
				try {
					_c.Close ();
				} finally {
					_c = null;					
				}
			}		
		}
				
		public IDataReader drQuery(string q)
		{
			IDbCommand command = _c.CreateCommand();
			IDataReader reader = null;
			
			command.CommandText = q;			
			Manager.i.info("Database", q);
			try {
				reader = command.ExecuteReader();
			} catch(Exception e) {
				Manager.i.error("Database", e);
			}
			return reader;
		}
		
		public QueryResult query(string q)
		{
			IDbCommand command = _c.CreateCommand();
			IDataReader reader = null;
			QueryResult result = new QueryResult(q);

			command.CommandText = q;
			Manager.i.info("Database", q);
			try {
				reader = command.ExecuteReader();
			} catch(Exception e) {
				Manager.i.error("Database", e);
			}
			
			if(reader != null) {
				while(reader.Read()) {
					NameValueCollection a = new NameValueCollection();
					
					for(int i = 0; i < reader.FieldCount; i++) {
						a[(reader.GetName(i))] = reader.GetValue(i).ToString();
					}
					result.Add(a);
				}
				reader.Close();
			}			
			return result;
		}
		
		public QueryResult query(string q, int begin, int n)
		{
			IDbCommand command = _c.CreateCommand();
			IDataReader reader = null;
			QueryResult result = new QueryResult(q);

			Manager.i.info("Database", q);
			try {							
				if(_listStrategy == "limit") {
					command.CommandText = q + " LIMIT " + begin + "," + n;
					reader = command.ExecuteReader();					
				} else {
					// by default, use cusor-way					
					command.CommandText = "BEGIN WORK";				
					command.ExecuteNonQuery();
				
					command.CommandText = "DECLARE w_cursor CURSOR FOR " + q;				
					command.ExecuteNonQuery();
				
					command.CommandText = "MOVE " + begin.ToString() + " FROM w_cursor";
					command.ExecuteNonQuery();
				
					command.CommandText = "FETCH " + n.ToString() + " FROM w_cursor";				
					reader = command.ExecuteReader();
				
					command.CommandText = "CLOSE w_cursor";				
					command.ExecuteNonQuery();
				
					command.CommandText = "COMMIT WORK";
					command.ExecuteNonQuery();								
				}
			} catch(Exception e) {
				Manager.i.error("Database", e);
			}
			
			if(reader != null) {
				while(reader.Read()) {
					NameValueCollection a = new NameValueCollection();
					
					for(int i = 0; i < reader.FieldCount; i++) {
						a[(reader.GetName(i))] = reader.GetValue(i).ToString();
					}
					result.Add(a);
				}
				reader.Close();
			}
			
			return result;
		}
				
		public void vQuery(string q)
		{
			IDbCommand command = _c.CreateCommand();

			command.CommandText = q;
			Manager.i.info("Database", q);
			try {
				command.ExecuteNonQuery();
			} catch(Exception e) {
				Manager.i.error("Database", e);
			}
		}	
		
		public void vQuery(Transaction t)
		{
						
			IDbCommand command = _c.CreateCommand();
			
			IDbTransaction dbt = _c.BeginTransaction();			
			try {
				IEnumerator i = t.queries.GetEnumerator();
				
				while(i.MoveNext()) {
					command.CommandText = (string) i.Current;
					command.ExecuteNonQuery();					
				}								
				dbt.Commit();
			} catch(Exception e) {
				dbt.Rollback();
				Manager.i.error("Database", e);
			}
		}	
		
		public int getTotalOf(string q)
		{
			IDbCommand command = _c.CreateCommand();
			IDataReader reader = null;
			int result = 0;

			command.CommandText = q;
			try {
				reader = command.ExecuteReader();
			} catch(Exception e) {
				Manager.i.error("Database", e);
			}
			
			if(reader != null) {
				reader.Read();
				reader.GetInt32(0);				
			}			
			reader.Close();
			
			return result;
		}
				
		public int lastId()
		{
			// ugly hack :)
			return getTotalOf("SELECT last_insert_id()"); 
		}
		
		public string esc(string s)
		{
			s.Replace("'","\'");
			s.Replace("\"","\\\"");
			return s;
		}

		public string unesc(string s)
		{
			s.Replace("\'","'");
			s.Replace("\\\"","\"");
			return s;
		}
	}
}
